<?php

/**
 * relatorio actions.
 *
 * @package    camaras
 * @subpackage relatorio
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 12479 2008-10-31 10:54:40Z fabien $
 */
class relatorioActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  
  public function executeList()
  {
    //Turn off the layout
    $this->setLayout(false);
 
    //My query
    $this->usuario_list = Doctrine::getTable('Usuario')
							->createQuery('a')
							->where('deleted = ?', 0)
							->andWhere('cd_tipo_usuario = ? ', 2)
							->orderBy('name')
							->execute();
 
  //Export the output in Excel 
  $this->getResponse()->setContentType('application/msexcel');
  $this->getResponse()->addHttpMeta('content-disposition: ', 'attachment; filename="relatorio.xls"', true);

}
 
  public function executeIndex(sfWebRequest $request)
  {
  	//replicado em relatório/index
    $this->camara_list = ($this->getUser()->getAttribute('gerente',null,'usr')) ?
	  Doctrine::getTable('Camara')
	    ->createQuery('a')
        ->orderBy('name')
	    ->execute()
	:
	  Doctrine::getTable('Camara')->getCamarasSecretario($this->getUser()->getAttribute('cd_usuario',null,'usr'));

  	//replicado em relatório/index
    $this->usuario_list = Doctrine::getTable('Usuario')
							->createQuery('a')
							->where('deleted = ?', 0)
							->andWhere('cd_tipo_usuario = ? ', 3)
							->orderBy('name')
							->execute();

  }

  public function executeResultado(sfWebRequest $request)
  {

    $this->setLayout(false);

	//tratando as datas
	if ($this->getRequestParameter('dt_start')) { 
		$this->dt_start = $this->getRequestParameter('dt_start');
		$this->dt_start = substr($this->dt_start,6,4).'/'.substr($this->dt_start,3,2).'/'.(substr($this->dt_start,0,2) - 1);
		$this->dt_start = date('Y-m-d h:i:s', strtotime($this->dt_start.' 23:59:59'));
	} else {
		$this->dt_start = null;
	}

	if ($this->getRequestParameter('dt_end')) { 
		$this->dt_end = $this->getRequestParameter('dt_end');
		$this->dt_end = substr($this->dt_end,6,4).'/'.substr($this->dt_end,3,2).'/'.substr($this->dt_end,0,2);
		$this->dt_end = date('Y-m-d h:i:s', strtotime($this->dt_end.' 23:59:59'));
	} else {
		$this->dt_end = null;
	}
		
	if ($this->getRequestParameter('tp_relatorio') == 2) { //analítico
		
		if ($this->getRequestParameter('cd_usuario')) {
			$q = Doctrine::getTable('Acao')->getAcoesSecretario($request->getParameter('cd_usuario'), $this->dt_start, $this->dt_end);
		} else {
			$q = Doctrine::getTable('Acao')->getAcoesPorCamara($request->getParameter('cd_camara'), $request->getParameter('cd_usuario'), $this->dt_start, $this->dt_end);
		}
		
		if (!$this->getUser()->getAttribute('gerente',null,'usr')) { $q->andWhere('c.cd_usr_secretario = ?', $this->getUser()->getAttribute('cd_usuario',null,'usr') ) ; }

		$this->acao_list = $q->execute();
			
	} else { //sintético
		
		$q = Doctrine_Query::create();

		$q->select('c.name camara, a.*')
		  ->addSelect('COUNT(a.cd_acao) qt_total_acao')
		  ->from("Acao a")
		  ->leftJoin('a.Proposicao p')
		  ->leftJoin('p.Camara c')
		  ;
		  
		  if (!$this->getUser()->getAttribute('gerente',null,'usr')) { $q->andWhere('c.cd_usr_secretario = ?', $this->getUser()->getAttribute('cd_usuario',null,'usr') ); }
		  if ($this->getRequestParameter('cd_camara'))       { $q->andWhere('p.cd_camara = ?', $this->getRequestParameter('cd_camara') ); }
		  if ($this->getRequestParameter('cd_usuario'))      { $q->andWhere('c.cd_usr_secretario = ?', $this->getRequestParameter('cd_usuario') ); }
		  if ($this->getRequestParameter('dt_start'))        { $q->andWhere('dt_limite_conclusao >= ?', $this->dt_start ); }
		  if ($this->getRequestParameter('dt_end'))          { $q->andWhere('dt_limite_conclusao <= ?', $this->dt_end ); }
		  
		  switch ($this->getRequestParameter('tp_agrupador')) {
			case 1:
				$q->groupBy('c.name, a.cd_acao, a.cd_proposicao, a.name, a.de_acao, a.dt_limite_conclusao, a.dt_conclusao, a.deleted, a.created_at , a.updated_at , a.slug');
				$q->orderBy('c.name');
				break;
			case 2:
				$q->groupBy('c.cd_usr_secretario, a.cd_acao, a.cd_proposicao, a.name, a.de_acao, a.dt_limite_conclusao, a.dt_conclusao, a.deleted, a.created_at , a.updated_at , a.slug, c.name');
				$q->orderBy('c.cd_usr_secretario');
				break;
		  };		
		
		$this->acao_list = $q->execute();
	
	}
	
	$this->tp_saida_excel = $this->getRequestParameter('tp_saida_excel');
	$this->tp_relatorio = $this->getRequestParameter('tp_relatorio');
	$this->tp_agrupador = $this->getRequestParameter('tp_agrupador');

	//Export the output in Excel 
	$this->getResponse()->setContentType('application/msexcel');
	$this->getResponse()->addHttpMeta('Content-Disposition: ', 'attachment; filename="relatorio.xls"', true);
	
  }
}

